 /**
  * *******************************************************************************
  * ***********************************策略SQL 回测**************************************
  * *用这个最新的，pgj第二天买的评估价 ，带验证，每天买前探针长度前30的，买入价参考评估价，卖出价待定*
  * 买入谨记一定要结合macd线，macd线如果离金叉很远的，果断放弃不要买。
  *
  * *******************************************************************************
  */
 
 CREATE OR REPLACE FUNCTION public.testsyl(integer)
 RETURNS void
 LANGUAGE plpgsql
 STRICT
AS $function$
declare
  days int :=$1;
  td date;
  gsy float8;
  bbsy float8;
begin
	for i in 1..days loop
		select avg(r.gsy),avg(r.bbsy),r.date from (
			 select --b.*,
			 b."date",b."close",b.low,b.volume,b.code,b.dz,b.macd,
			 (select low from basic_data where to_date(date,'YYYY-MM-DD')= getwd(current_date,i-1) and code=b.code) as 次日low, -- 仅供测,
			  b."close" > (select low from basic_data where to_date(date,'YYYY-MM-DD')= getwd(current_date,i-1) and code=b.code) as 是否买到,-- 仅供测试
			  ((select high from basic_data where to_date(date,'YYYY-MM-DD')= getwd(current_date,i-2) and code=b.code)-b."close")/ b."close" as gsy ,-- 仅供测试 
			  ((select "open" from basic_data where to_date(date,'YYYY-MM-DD')= getwd(current_date,i-2) and code=b.code)-b."close")/ b."close" as bbsy, -- 仅供测试
			 --((select low from basic_data where to_date(date,'YYYY-MM-DD')= current_date-2 and code=b.code)-b.low)/b.low*100 as "最低价波动指数", -- 仅供测试
			 s."name",to_date(to_char(s."timeToMarket",'99999999'),'YYYYMMDD') as "上市日期" ,
			 case when high=low then 0
				 else  (LEAST(open,close)-low)/(high-low)* 100   
				 end as dz2,
				case when high=low then 0
				 else  (high-GREATEST(open,close))/(high-low)* 100   
				 end as sdz
			 from basic_data b --where 
			 left join stocks s on b.code=s.code 
			 where 
			 /**s."timeToMarket" <>0 and to_date(to_char(s."timeToMarket",'99999999'),'YYYYMMDD') < current_date-180  --排除新上市的,
			 and macd != 'NaN'--macd值为NaN时，好像股票前段时间都有涨停。具体为什么为NaN待确认
			 and macd::float8> (select max(macd::float8) from basic_data where to_date(date,'YYYY-MM-DD') >= getwd(current_date,i+3) and  to_date(date,'YYYY-MM-DD') < getwd(current_date,i) and code=b.code group by code) --MACD 近4天最小的,尽可能的排除macd返阴的
			 and macd::float8 <= 0 and  macd::float8 >=-0.2  
			 and to_date(b.date,'YYYY-MM-DD')= getwd(current_date,i)  --根据计算时间减去相应的天数，如果是当天计算就不用减
			 and dz2>50 and dz2 <80*/
			   to_date(b.date,'YYYY-MM-DD')= getwd(current_date,i)  --根据计算时间减去相应的天数，如果是当天计算就不用减
			 and s."timeToMarket" <>0 and to_date(to_char(s."timeToMarket",'99999999'),'YYYYMMDD') < current_date-180  --排除新上市的,
			 and macd != 'NaN'--macd值为NaN时，好像股票前段时间都有涨停。具体为什么为NaN待确认
			 and macd::float8> (select max(macd::float8) from basic_data where to_date(date,'YYYY-MM-DD') >= getwd(current_date,i+3) and  to_date(date,'YYYY-MM-DD') < getwd(current_date,i) and code=b.code group by code) /**MACD 近4天最小的,尽可能的排除macd返阴的*/
			 and macd::float8 <=0  --and macd::float8 >= -0.02 -- macd尽可能的接近0
			 and 0.1 < (select max(macd::float8) from basic_data where to_date(date,'YYYY-MM-DD') >= getwd(current_date,i+19) and  to_date(date,'YYYY-MM-DD') < getwd(current_date,i) and code=b.code group by code) /**MACD 近20天有在0以上的，排除长时间阴线的*/
			 and 0 > (select zd from basic_data where to_date(date,'YYYY-MM-DD') = getwd(current_date,i) and code=b.code) --当天交易跌的
			 and 0 > (select zd from basic_data where to_date(date,'YYYY-MM-DD') = getwd(current_date,i+1) and code=b.code) --昨天交易跌的
			 order by dz2 desc  limit 10
		) as r group by r.date into gsy,bbsy,td;
		raise notice 'syl : %, %,%', to_char(gsy,'00.999'),to_char(bbsy,'00.999'),td;
		insert into  testsy values(gsy,bbsy,td);
		--commit;
	end loop;
end 
$function$